﻿CREATE PROCEDURE [dbo].[select_store_flow]
	@in_date smalldatetime
AS
	declare @result_table table(result_id bigint identity(1,1) primary key,
								result_code nvarchar(20) not null,
								result_level nvarchar(20) not null,
								result_in decimal(5,1) default(0),
								result_out decimal(5,1) default(0),
								result_location nvarchar(20) not null,
								result_ncust nvarchar(20) not null,
								result_agent nvarchar(20) not null,
								result_car nvarchar(20) not null);
	declare @code nvarchar(20);
	declare @level nvarchar(20);
	declare @location nvarchar(20);
	declare @ncust nvarchar(20);
	declare @agent nvarchar(20);
	declare @car nvarchar(20);
	declare @weight_in decimal(5,1);
	declare @weight_out decimal(5,1);
	
	declare code_cursor cursor for
	select inout_code, inout_level, inout_location, inout_agent, inout_car,
	inout_in, inout_out, inout_ncust from tb_inout where inout_date = @in_date;
	open code_cursor;
	fetch next from code_cursor into @code, @level, @location, @agent, @car, @weight_in, @weight_out, @ncust;
	while @@fetch_status = 0 begin
	
		select @weight_in = isnull(sum(inout_in),0) from tb_inout
		where inout_date = @in_date and inout_code = @code;
		
		select @weight_out = isnull(sum(inout_out),0) from tb_inout
		where inout_date = @in_date and inout_code = @code;

		insert into @result_table(result_code, result_level, result_in,result_out,result_location,result_ncust,result_agent,result_car)
		values (@code,@level,@weight_in,@weight_out,@location,@ncust,@agent,@car);

		fetch next from code_cursor into @code, @level, @location, @agent, @car, @weight_in, @weight_out, @ncust;
	end
	
	close code_cursor;
	deallocate code_cursor;
	
	select * from @result_table;